package kt.processor;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import kt.dal.DBPoolNotFoundException;
import kt.dal.DBPoolX;
import kt.dal.DBPoolXName;
import kt.processor.entity.Answer;
import kt.processor.entity.Enums;
import kt.processor.entity.NqhException;
import kt.processor.entity.Question;
import kt.processor.entity.SessionIQ;
import kt.smpp.pdu.SMSRequest;
import kt.smpp.pdu.SMSResponse;
import kt.util.SmsUtil;

import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;


/***
 * This class processes registering and playing the millionaire game
 * 
 * @author HoaNguyen
 * 
 */
public class MillionareUnlimitProcessor { //extends Processor {

	private static final String SERVICE_NO = "1795";

	static final String INVALID_SYNTAX = "Invalid Syntax, plz send E to 1795 to register a new game in English, send K to 1795 if your phone support Khmer. Call 1777 for detail.";

	private static Logger logger = LogManager.getLogger(MillionareUnlimitProcessor.class);
	
//	public MillionareUnlimitProcessor() {
//		setResponseQueue(Server.getResponseQueue());
//	}
//
//	public MillionareUnlimitProcessor(Queue requestQueue, Queue responseQueue) {
//		setRequestQueue(requestQueue);
//		setResponseQueue(responseQueue);
//	}
//
//	public void run() {
//		SMSRequest request = null;
//		Collection vResponses = null;
//		while (Server.isRunning())
//			try {
//				request = (SMSRequest) getRequestQueue().dequeue();
//				vResponses = processRequest(request);
//				for (Iterator it = vResponses.iterator(); it.hasNext();)
//					getResponseQueue().enqueue(it.next());
//			} catch (Exception ex) {
//				ex.printStackTrace();
//			}
//	}

	// String[] PRICES = new String[] {"1P",
	// "2$","4$","8$","16$","20$","40$","80$","160$","320$","400$","600$","800$","1000$","1500$"};
	public static int[] POINTS = new int[] { 1, 2, 3, 4, 5, 10, 12, 14, 16, 18,
			25, 30, 35, 40, 45 };

	public ArrayList<SMSResponse> processRequest(SMSRequest request) {
		logger.info("Begin process request "  + request);
		ArrayList<SMSResponse> vResponses = new ArrayList<SMSResponse>();
		String info = request.getInfo();
		String isdn = request.getSenderNumber();
		SMSResponse response = request.getResponse();
		Question q;
		try {
			if(isdn.equalsIgnoreCase("855979066460") || isdn.equalsIgnoreCase("855978024515") 
					|| isdn.equalsIgnoreCase("855973828597") || isdn.equalsIgnoreCase("855887493899")) {
				if(info.equals("SEND QUESTIONS")){
					logger.info("Create question and send to all customers");
					int re = adminSendQuestionToAllCustomers();
					response.setInfo("Question sent: " + re);
					response.setMessageType(1);
					vResponses.add(response);
					return vResponses;
				}
			}
			boolean isFirstTime = isFirstTime(isdn); 
			if(isFirstTime) {
				SmsUtil.sendSms(isdn, "Congrate! You have signed up to join IQ Millionaire game, you have 01 point = 01 lucky draw chance to win 3,000,000 Riels. More details, call 1777.", SERVICE_NO, request.getMobileOperator());
				Thread.sleep(500);
				SmsUtil.sendSms(isdn, "Please answer the question of the game, each correct answer you have more x points. Each point = 01 chance to win 3,000,000 Riels. For help, call 1777.", SERVICE_NO, request.getMobileOperator());
				Thread.sleep(500);
			}
			if (info.toUpperCase().startsWith("E") || info.toUpperCase().startsWith("K") || info.toUpperCase().startsWith("IQ")) { // "E".equalsIgnoreCase(info))
				logger.info("get English question and start new session");
				q = getNewQuestion(isdn, Enums.Language.ENGLISH);
				removeFromCancelList(isdn);
				response.setInfo("" + q.getPoints() + "P:" + q.getQuestion());
			} else if (info.toUpperCase().startsWith("K")) {
				logger.info("get KHMER question and start new session");
				q = getNewQuestion(isdn, Enums.Language.KHMER);
				removeFromCancelList(isdn);
				response.setInfo("" + q.getPoints() + "P:" + q.getQuestion_KH());
			} else if (info.toUpperCase().startsWith("P")) { // get the points
				logger.info("get total point of " + isdn);
				getThePoint(isdn, response);
			} else if (info.toUpperCase().startsWith("OFF")) { // get the points
				logger.info("customer want to OFF the service " + isdn);
				cancelService(isdn);
				response.setInfo("Thank you for using Millionaire service! You will no more receive SMS from us. To register again, please send K (Khmer) or E (English) to " + SERVICE_NO);
			} else if (isWaiting4Answer(isdn)) {
				removeFromCancelList(isdn);
				processAnswer(request, vResponses, isdn, response);
			} else {
				removeFromCancelList(isdn);
				String lang = getLastCorrectSessionLanguage(isdn);
				if(Enums.Language.KHMER.equalsIgnoreCase(lang)) {
					q = getNewQuestion(isdn, Enums.Language.KHMER);
					response.setInfo("" + q.getPoints() + "P:" + q.getQuestion_KH());
				} else {
					q = getNewQuestion(isdn, Enums.Language.ENGLISH);
					response.setInfo("" + q.getPoints() + "P:" + q.getQuestion());
				}
			}
		} catch (Exception e) {
			logger.fatal("error while process", e);
		}

		response.setMessageType(1);
		vResponses.add(response);
		logger.info("End process request "  + request);
		return vResponses;
	}

	

	private boolean isFirstTime(String isdn) {
		logger.info("check for the first time in this week? " + isdn);
		DBPoolX smsPool = null;
		Connection cnn = null;
		int re = 0;
		try {
			smsPool = DBPoolX.getInstance(DBPoolXName.SERVICE_DEFAULT);
			cnn = smsPool.getConnection();
			cnn.setAutoCommit(true);
			PreparedStatement preStmt = null;
			preStmt = cnn
					.prepareStatement("select count(*) from MIL_ANSWER WHERE ISDN = ? AND SEASON_ORDER = 0");
			preStmt.setString(1, isdn);
			ResultSet rs = preStmt.executeQuery();
			if (rs.next()) {
				re = rs.getInt(1);
			}
			preStmt.close();
			cnn.close();
		} catch (DBPoolNotFoundException e) {
			logger.error("error isFirstTime", e);
		} catch (Exception e) {
			logger.error("error isFirstTime", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(cnn, null);
			}
		}
		logger.info("check for the first time in this week? result = " + isdn + (re < 1));
		return re < 1;
	}

	private void cancelService(String isdn) {
		DBPoolX smsPool = null;
		Connection cnn = null;		
		try {
			smsPool = DBPoolX.getInstance(DBPoolXName.SERVICE_DEFAULT);
			cnn = smsPool.getConnection();
			cnn.setAutoCommit(false);
			abortAnySession(isdn, cnn, false);
			updateAnswer(isdn, Enums.AnsStatus.ABORTED, cnn);
			addToCancelList(isdn, cnn);
			cnn.commit();
			cnn.close();
		} catch (DBPoolNotFoundException e) {
			logger.error("error when getTotalPoint", e);
		} catch (Exception e) {
			logger.error("error when getTotalPoint", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(cnn, null);
			}
		}	
	}

	private void removeFromCancelList(String isdn) throws SQLException {
		if(logger.isDebugEnabled()) {
			logger.debug("remove " + isdn + " from MIL_CANCEL table");
		}
		DBPoolX smsPool = null;
		Connection cnn = null;
		try {
			smsPool = DBPoolX.getInstance(DBPoolXName.SERVICE_DEFAULT);
			cnn = smsPool.getConnection();
			cnn.setAutoCommit(true);
			PreparedStatement preStmt = null;		
			preStmt = cnn.prepareStatement("delete from MIL_CANCEL where ISDN = ?");
			preStmt.setString(1, isdn);
			preStmt.executeUpdate();
			preStmt.close();
			cnn.close();
		} catch (DBPoolNotFoundException e) {
			logger.error("error when removeFromCancelList", e);
		} catch (Exception e) {
			logger.error("error when removeFromCancelList", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(cnn, null);
			}
		}
	}
	
	private void addToCancelList(String isdn, Connection cnn) throws SQLException {
		PreparedStatement preStmt = null;		
		preStmt = cnn.prepareStatement("INSERT INTO MIL_CANCEL ( ISDN, CREATE_DATE )  VALUES (?, now())");
		preStmt.setString(1, isdn);
		preStmt.executeUpdate();
		preStmt.close();
	}

	String getLastCorrectSessionLanguage(String isdn) {
		DBPoolX smsPool = null;
		Connection cnn = null;
		String re = "";
		try {
			smsPool = DBPoolX.getInstance(DBPoolXName.SERVICE_DEFAULT);
			cnn = smsPool.getConnection();
			cnn.setAutoCommit(true);
			PreparedStatement preStmt = null;
			preStmt = cnn
					.prepareStatement("select s.LANGUAGE, a.ID from MIL_SESSION s, MIL_ANSWER a " +
							"where a.SESSION_ID = s.ID AND a.ANSWER_STATUS = ? AND s.ISDN = ? order by a.ID desc limit 1");
			preStmt.setString(1, Enums.AnsStatus.CORRECT_ANS);
			preStmt.setString(2, isdn);
			ResultSet rs = preStmt.executeQuery();
			if (rs.next()) {
				re = rs.getString("LANGUAGE");
			}
			preStmt.close();
			cnn.close();
		} catch (DBPoolNotFoundException e) {
			logger.error("error when getTotalPoint", e);
		} catch (Exception e) {
			logger.error("error when getTotalPoint", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(cnn, null);
			}
		}
		if(Enums.Language.KHMER.equalsIgnoreCase(re)) {
			return Enums.Language.KHMER;
		} else {
			return Enums.Language.ENGLISH;
		}
	}
	
	boolean isWaiting4Answer(String isdn) {
		logger.info("check having question waiting for answer? " + isdn);
		DBPoolX smsPool = null;
		Connection cnn = null;
		int re = 0;
		try {
			smsPool = DBPoolX.getInstance(DBPoolXName.SERVICE_DEFAULT);
			cnn = smsPool.getConnection();
			cnn.setAutoCommit(true);
			PreparedStatement preStmt = null;
			preStmt = cnn
					.prepareStatement("select count(*) from MIL_ANSWER a, MIL_SESSION s " + 
							"WHERE a.SESSION_ID = s.ID AND a.SEASON_ORDER = 0 AND a.ANSWER_STATUS = ? AND a.ISDN = ? AND s.STATUS = ?");
			preStmt.setString(1, Enums.AnsStatus.WAITING);
			preStmt.setString(2, isdn);
			preStmt.setString(3, Enums.SessionStatus.PLAYING);
			ResultSet rs = preStmt.executeQuery();
			if (rs.next()) {
				re = rs.getInt(1);
			}
			preStmt.close();
			cnn.close();
		} catch (DBPoolNotFoundException e) {
			logger.error("error when getTotalPoint", e);
		} catch (Exception e) {
			logger.error("error when getTotalPoint", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(cnn, null);
			}
		}
		logger.info("check having question waiting for answer? " + isdn + (re == 1));
		return re == 1;
	}

	private void getThePoint(String isdn, SMSResponse response) {
		int point = getTotalPoint(isdn);
		//Your total points: xx. Answer more questions to get more points and chances to win 3,000,000 Riel and other prizes every week. More details, plz call 1777.
		response.setInfo("Your total points: "
						+ point
						+ ". Answer more questions to get more points and chances to win 3,000,000 Riel and other prizes every week. More details, plz call 1777.");
	}

	int getTotalPoint(String isdn) {
		DBPoolX smsPool = null;
		Connection cnn = null;
		int re = 0;
		try { //
			smsPool = DBPoolX.getInstance(DBPoolXName.SERVICE_DEFAULT);
			cnn = smsPool.getConnection();
			cnn.setAutoCommit(true);
			PreparedStatement preStmt = null;
			preStmt = cnn.prepareStatement("select sum(POINTS) from MIL_ANSWER WHERE SEASON_ORDER = 0 AND ANSWER_STATUS = ? AND ISDN = ?");
			preStmt.setString(1, Enums.AnsStatus.CORRECT_ANS);
			preStmt.setString(2, isdn);
			ResultSet rs = preStmt.executeQuery();
			if (rs.next()) {
				re = rs.getInt(1);
			}
			preStmt.close();
			cnn.close();
		} catch (DBPoolNotFoundException e) {
			logger.error("error when getTotalPoint", e);
		} catch (Exception e) {
			logger.error("error when getTotalPoint", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(cnn, null);
			}
		}
		return re;
	}

	private void processAnswer(SMSRequest request, ArrayList<SMSResponse> vResponses,
			String isdn, SMSResponse response) {
		logger.info("Process answer for request " + request);
		DBPoolX smsPool = null;
		Connection cnn = null;
		try {
			smsPool = DBPoolX.getInstance(DBPoolXName.SERVICE_DEFAULT);
			cnn = smsPool.getConnection();
			cnn.setAutoCommit(false);
			String ans = request.getInfo();
			SessionIQ sess = getCurrentSession(isdn, cnn);
			Answer waitingAns = getWaitingAns(sess.getId(), cnn);
			logger.info("The waiting answer is" + waitingAns);
			// Question ques = getPreviousQues(isdn, cnn);
			boolean isCorrect = waitingAns.getExpected_ans().equalsIgnoreCase(ans);
			boolean isOvertime = false; //isTimeoutAns(waitingAns);// .getAnswer_starttime().equals(ans);
			if (isOvertime) {
				// start a new auto session
				logger.info("Over time");
				processTimeout(request, vResponses, isdn, response, cnn, ans,sess, waitingAns);
			} else {
				if (isCorrect) {
					logger.info("Correct answer");
					processCorrect(response, cnn, ans, sess, waitingAns);
				} else {
					logger.info("Incorrect answer");
					processIncorrect(request, vResponses, isdn, response, sess, waitingAns, ans, cnn);
				}
			}
			cnn.commit();
			cnn.close();
		} catch (SQLException e) {
			SmsUtil.Rollback(cnn);
			logger.error("error when processAnswer", e);
		} catch (DBPoolNotFoundException e) {
			logger.error("error when processAnswer", e);
		} catch (Exception e) {
			SmsUtil.Rollback(cnn);
			logger.error("error when processAnswer", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(cnn, null);
			}
		}
		logger.info("End processing answer for request " + request);
	}

	private void processIncorrect(SMSRequest request,
			ArrayList<SMSResponse> vResponses, String isdn,
			SMSResponse response, SessionIQ sess, Answer waitingAns,
			String ans, Connection cnn) throws SQLException {
		logger.info("Process wrong answer and start new session");
		Question q = wrongAnsAndStartNewSession(sess, isdn, waitingAns.getId(),
				ans, cnn);
		logger.info("update the status of current session to Incorrect");
		wrongAns(sess, isdn, waitingAns.getId(), ans, cnn);
		
		//Incorrect Answer, total points: XX. 3,000,000 riel and other prizes this week is closer to you than ever. Call 1777 for detail
		String smsInfo = "Incorrect Answer, total points: "
			+ getTotalPoint(request.getSenderNumber())
			+ ". 3,000,000 and other prizes this week is closer to you than ever! Call 1777 for detail!";
		logger.info("send SMS to customer about total points " + smsInfo);
		SmsUtil.sendSms(isdn, smsInfo, SERVICE_NO, request.getMobileOperator());
		logger.info ("Sleep 10 seconds and send new question " + q);
		try {
			Thread.sleep(5000);
		} catch (InterruptedException e) {
			logger.error("someone interupt the thread", e);
		}
		response.setInfo(updateWrongAnsResponse(request,
				sess.getNo_of_ques_ans()).getInfo());

		// TODO: need to send the new session immediately
		if (Enums.Language.KHMER.equalsIgnoreCase(sess.getLanguage())) {
			response.setInfo("Next " + q.getPoints() + "P:" + q.getQuestion_KH());
		} else {
			response.setInfo("Next " + q.getPoints() + "P:" + q.getQuestion());
		}
	}

	private void processCorrect(SMSResponse response, Connection cnn, String ans, SessionIQ sess,
			Answer waitingAns) throws SQLException {
		Question q = getNextQuestion(sess, waitingAns, ans, cnn);
		//int noOfQ = sess.getNo_of_ques_ans() + 2;

		if (Enums.Language.KHMER.equalsIgnoreCase(sess.getLanguage())) {
			response.setInfo("Correct, Next " + q.getPoints() + "P:" + q.getQuestion_KH());
		} else {
			response.setInfo("Correct, Next " + q.getPoints() + "P:" + q.getQuestion());
		}		
	}

	private void processTimeout(SMSRequest request, ArrayList<SMSResponse> vResponses,
			String isdn, SMSResponse response, Connection cnn, String ans,
			SessionIQ sess, Answer waitingAns) throws SQLException {
		Question q;
		vResponses.add(updateOverTimeResponse(request, sess.getNo_of_ques_ans()));
		q = startNewAutoSession(sess, isdn, waitingAns.getId(), ans, cnn);
		if (Enums.Language.KHMER.equalsIgnoreCase(sess.getLanguage())) {
			response.setInfo("Q1,1P: " + q.getQuestion_KH());
		} else {
			response.setInfo("Q1,1P: " + q.getQuestion());
		}
	}

	private void closeSession(int sessionId, String reasonStatus, Connection cnn)
			throws SQLException {
		PreparedStatement preStmt = null;
		preStmt = cnn.prepareStatement("update MIL_SESSION set STATUS = ?, SESSION_END_TIME = now() where ID = ?");
		preStmt.setString(1, reasonStatus);
		preStmt.setInt(2, sessionId);
		preStmt.executeUpdate();
		preStmt.close();
	}

	private void updateAnsStatus(int ansID, String ans, String status, Connection cnn) throws SQLException {
		PreparedStatement preStmt = null;
		preStmt = cnn.prepareStatement("update MIL_ANSWER set ANSWER_STATUS = ?, ANSWER_ENDTIME = now(), CUSTOMER_ANS = ? where ID = ?");
		preStmt.setString(1, status);
		preStmt.setString(2, ans);
		preStmt.setInt(3, ansID);
		preStmt.executeUpdate();
		preStmt.close();
	}

	private SessionIQ getCurrentSession(String isdn, Connection cnn)
			throws SQLException {
		PreparedStatement preStmt = null;
		SessionIQ re = null;

		String strSQL = " select ID, ISDN, STATUS, LAST_QUES_ID, LAST_SEND_TIME, NO_OF_QUES_ANS, SESSION_START_TIME, LANGUAGE"
				+ " from MIL_SESSION where ISDN = ? and STATUS = ? order by ID desc";
		preStmt = cnn.prepareStatement(strSQL);
		preStmt.setString(1, isdn);
		preStmt.setString(2, Enums.SessionStatus.PLAYING);
		ResultSet rs = preStmt.executeQuery();
		if (rs.next()) {
			re = new SessionIQ();
			re.setId(rs.getInt("ID"));
			re.setIsdn(rs.getString("ISDN"));
			re.setStatus(rs.getString("STATUS"));
			//re.setLucky_code(rs.getString("LUCKY_CODE"));
			re.setLast_ques_id(rs.getInt("LAST_QUES_ID"));
			re.setLast_send_time(rs.getDate("LAST_SEND_TIME"));
			re.setNo_of_ques_ans(rs.getInt("NO_OF_QUES_ANS"));
			re.setSession_start_time(rs.getDate("SESSION_START_TIME"));
			re.setLanguage(rs.getString("LANGUAGE"));
		}
		rs.close();
		preStmt.close();
		return re;
	}

	private Answer getWaitingAns(int sessID, Connection cnn)
			throws SQLException {
		PreparedStatement preStmt = null;
		Answer re = null;

		String strSQL = " select ID,ISDN,QUESTION_ID,ANSWER_ORDER,ANSWER_STATUS,SESSION_ID,ANSWER_STARTTIME,ANSWER_ENDTIME, CUSTOMER_ANS, EXPECTED_ANS"
				+ " from MIL_ANSWER where SESSION_ID = ? and ANSWER_STATUS = ? order by ID desc";
		preStmt = cnn.prepareStatement(strSQL);
		preStmt.setInt(1, sessID);
		preStmt.setString(2, Enums.AnsStatus.WAITING);
		ResultSet rs = preStmt.executeQuery();
		if (rs.next()) {
			re = new Answer();
			re.setId(rs.getInt("ID"));
			re.setIsdn(rs.getString("ISDN"));
			re.setQuestion_id(rs.getInt("QUESTION_ID"));
			re.setAnswer_order(rs.getInt("ANSWER_ORDER"));
			re.setAnswer_status(rs.getString("ANSWER_STATUS"));
			re.setSession_id(rs.getInt("SESSION_ID"));
			re.setAnswer_starttime(rs.getTimestamp("ANSWER_STARTTIME"));
			re.setCustomer_ans(rs.getString("CUSTOMER_ANS"));
			re.setExpected_ans(rs.getString("EXPECTED_ANS"));
		}
		rs.close();
		preStmt.close();
		return re;
	}

	private SMSResponse updateOverTimeResponse(SMSRequest req, int noOfAns) {
		SMSResponse res = req.getResponse();
		res.setInfo("Time to answer are over, total points: "+ getTotalPoint(req.getSenderNumber()) + 
				". Your new turn will start in a moment. 2,000,000 this month is closer to you than ever!");
		return res;
	}

	private SMSResponse updateWrongAnsResponse(SMSRequest req, int noOfAns) {
		SMSResponse res = req.getResponse();
		res.setInfo("Incorrect answer, total points: " + getTotalPoint(req.getSenderNumber()) + 
				". Your new turn will start in a moment. 2,000,000 this month is closer to you than ever!");
		return res;
	}


	private Question startNewAutoSession(SessionIQ session, String isdn,
			int ansId, String ans, Connection cnn) throws SQLException {
		updateAnsStatus(ansId, ans, Enums.AnsStatus.TIMEOUT, cnn);
		closeSession(session.getId(), Enums.SessionStatus.TIMEOUT, cnn);
		// get random question
		Question q = getRandomQuestion(isdn, cnn, session.getLanguage());
		int sessionId = addNewSession(isdn, q.getId(), session.getLanguage(),cnn);
		addNewAns(isdn, q, sessionId, cnn);
		return q;
	}

	private Question getNextQuestion(SessionIQ sess, Answer ans, String cus_ans,
			Connection cnn) throws SQLException {
		// update answer
		//updateAnsStatus(ans.getQuestion_id(), cus_ans, Enums.AnsStatus.CORRECT_ANS, sess.getQuestionPoint(), cnn);
		
		updateAnsStatus(ans.getId(), cus_ans, Enums.AnsStatus.CORRECT_ANS, cnn);

//		int level = Enums.QuesLevel.One;
//		if (sess.getNo_of_ques_ans() + 1 >= 10) {
//			level = Enums.QuesLevel.Three;
//		} else if (sess.getNo_of_ques_ans() + 1 >= 5) {
//			level = Enums.QuesLevel.Two;
//		}
		Question q = getRandomQuestion(sess.getIsdn(), cnn, sess.getLanguage());
		inscreaseQuestion4Session(sess.getId(), q.getId(), cnn);
		addNewAns(sess.getIsdn(), q, sess.getId(), cnn);
		return q;
	}

	private void inscreaseQuestion4Session(int sessionID, int newQuesId,
			Connection cnn) throws SQLException {
		PreparedStatement preStmt = null;
		String sql = "update MIL_SESSION set LAST_QUES_ID = ?, LAST_SEND_TIME = now(), NO_OF_QUES_ANS = NO_OF_QUES_ANS + 1 where ID = ?";
		preStmt = cnn.prepareStatement(sql);
		preStmt.setInt(1, newQuesId);
		preStmt.setInt(2, sessionID);
		preStmt.executeUpdate();
		preStmt.close();
	}

	private Question wrongAnsAndStartNewSession(SessionIQ sess, String isdn,
			int ansId, String ans, Connection cnn) throws SQLException {
		updateAnsStatus(ansId, ans, Enums.AnsStatus.INCORRECT_ANS, cnn);
		//closeSession(sess.getId(), Enums.SessionStatus.WRONG_ANS, cnn);
		// get random question
		Question q = getRandomQuestion(isdn, cnn, sess.getLanguage());
		//int sessionId = addNewSession(isdn, q.getId(), sess.getLanguage(), cnn);
		addNewAns(isdn, q, sess.getId(), cnn);
		return q;
	}

	/**
	 * @param sess
	 * @param isdn
	 * @param ansId
	 * @param ans
	 * @param cnn
	 * @return total point the customer get until now
	 * @throws SQLException
	 */
	private int wrongAns(SessionIQ sess, String isdn, int ansId, String ans,
			Connection cnn) throws SQLException {
		updateAnsStatus(ansId, ans, Enums.AnsStatus.INCORRECT_ANS, cnn);
		//no need to close the session
		//closeSession(sess.getId(), Enums.SessionStatus.WRONG_ANS, cnn);
		return getTotalPoint(isdn);
	}

	/**
	 * Abort current waiting answer and session (if have) and start new session,
	 * get new question
	 * 
	 * @param isdn
	 * @param lang
	 *            language of the question
	 * @return new question
	 * @throws NqhException
	 */
	private Question getNewQuestion(String isdn, String lang)
			throws NqhException {
		Question q = null;
		DBPoolX smsPool = null;
		Connection cnn = null;
		try {
			smsPool = DBPoolX.getInstance(DBPoolXName.SERVICE_DEFAULT);
			cnn = smsPool.getConnection();
			cnn.setAutoCommit(false);
			// update session
			abortAnySession(isdn, cnn, false);
			updateAnswer(isdn, Enums.AnsStatus.ABORTED, cnn);
			q = getRandomQuestion(isdn, cnn, lang);
			int sessionId = addNewSession(isdn, q.getId(), lang, cnn);
			addNewAns(isdn, q, sessionId, cnn);
			cnn.commit();
			cnn.close();
		} catch (Exception e) {
			if (e instanceof NqhException) {
				throw (NqhException) e;
			} else {
				SmsUtil.Rollback(cnn);
				logger.error("error when processBID", e);
			}
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(cnn, null);
			}
		}
		logger.info("question returned " + q);
		return q;
	}

	private void abortAnySession(String isdn, Connection cnn, boolean isBySystem)
			throws SQLException {
		PreparedStatement preStmt = null;

		preStmt = cnn.prepareStatement("update MIL_SESSION set STATUS = ?, SESSION_END_TIME = now() where ISDN = ? and STATUS = ?");
		if(isBySystem){
			preStmt.setString(1, Enums.SessionStatus.ABORTED_SYS);
		} else {
			preStmt.setString(1, Enums.SessionStatus.ABORTED);
		}		
		preStmt.setString(2, isdn);
		preStmt.setString(3, Enums.SessionStatus.PLAYING);
		preStmt.executeUpdate();
		preStmt.close();
	}

	private void updateAnswer(String isdn, String newStatus, Connection cnn)
			throws SQLException {
		PreparedStatement preStmt = null;

		preStmt = cnn
				.prepareStatement("update MIL_ANSWER set ANSWER_STATUS = ?, ANSWER_ENDTIME = now() where ANSWER_STATUS = ? and ISDN = ?");
		preStmt.setString(1, Enums.AnsStatus.ABORTED);
		preStmt.setString(2, Enums.AnsStatus.WAITING);
		preStmt.setString(3, isdn);
		preStmt.executeUpdate();
		preStmt.close();
	}

	private Question getRandomQuestion(String isdn, Connection cnn, String lang) throws SQLException {
		PreparedStatement preStmt = null;
		Question re = null;

		String strSQL = "SELECT ID, QUESTION, QUESTION_KH, ANSWER, POINTS FROM MIL_QUESTION where QUESTION is not null and QUESTION != '' "
			+ " and QUESTION_KH is not null and QUESTION_KH != ''"
			+ " and ID not in (select QUESTION_ID from MIL_ANSWER where ISDN = ?) order by rand() limit 1";
		preStmt = cnn.prepareStatement(strSQL);
		preStmt.setString(1, isdn);
		ResultSet rs = preStmt.executeQuery();
		if (rs.next()) {
			re = new Question();
			re.setId(rs.getInt(1));
			re.setQuestion(rs.getString(2));
			re.setQuestion_KH(rs.getString(3));
			re.setAnswer(rs.getString(4));
			re.setPoints(rs.getInt(5));
			//re.setLevel(level);
		}
		rs.close();
		preStmt.close();
		if (re != null) {
			return re;
		} else {
			return getRandomQuestionNoFilter(cnn);
		}
	}

	private Question getRandomQuestionNoFilter(Connection cnn)
			throws SQLException {
		PreparedStatement preStmt = null;
		Question re = null;

		String strSQL = "SELECT ID, QUESTION, QUESTION_KH, ANSWER FROM MIL_QUESTION where QUESTION is not null and QUESTION != '' "
			+ " and QUESTION_KH is not null and QUESTION_KH != '' "
			+" order by rand() limit 1";
		preStmt = cnn.prepareStatement(strSQL);
		ResultSet rs = preStmt.executeQuery();
		if (rs.next()) {
			re = new Question();
			re.setId(rs.getInt(1));
			re.setQuestion(rs.getString(2));
			re.setQuestion_KH(rs.getString(3));
			re.setAnswer(rs.getString(4));
			//re.setLevel(level);
		}
		rs.close();
		preStmt.close();

		return re;
	}

	private int addNewSession(String isdn, int lastQuesId, String lang,
			Connection cnn) throws SQLException {
		PreparedStatement preStmt = null;
		String strSQL = "INSERT INTO MIL_SESSION (ISDN, STATUS, LUCKY_CODE_L1, LAST_QUES_ID, LAST_SEND_TIME, NO_OF_QUES_ANS, SESSION_START_TIME, SESSION_END_TIME, LANGUAGE)"
				+ " VALUES (?, ?, null, ?, now(), 0, now(), null, ?) ";

		preStmt = cnn.prepareStatement(strSQL);

		preStmt.setString(1, isdn);
		preStmt.setString(2, Enums.SessionStatus.PLAYING);// STATUS
		preStmt.setInt(3, lastQuesId); // LAST_QUES_ID
		preStmt.setString(4, lang); // LANGUAGE
		preStmt.executeUpdate();
		preStmt.close();
		return getInsertedSession(isdn, cnn);
	}

	private int getInsertedSession(String isdn, Connection cnn)
			throws SQLException {
		PreparedStatement preStmt = null;
		int re = 0;

		String strSQL = "select max(id) from MIL_SESSION where ISDN = ? and STATUS = ?";
		preStmt = cnn.prepareStatement(strSQL);
		preStmt.setString(1, isdn);
		preStmt.setString(2, Enums.SessionStatus.PLAYING);
		ResultSet rs = preStmt.executeQuery();
		if (rs.next()) {
			re = rs.getInt(1);
		}
		rs.close();
		preStmt.close();
		return re;
	}

	private void addNewAns(String isdn, Question q, int sessionId,
			Connection cnn) throws SQLException {
		PreparedStatement preStmt = null;
		String strSQL = "INSERT INTO MIL_ANSWER (ISDN, QUESTION_ID, ANSWER_ORDER, ANSWER_STATUS, SESSION_ID, ANSWER_STARTTIME, ANSWER_ENDTIME, EXPECTED_ANS, POINTS) "
				+ "VALUES (?, ?, ?, ?, ?, now(), null, ?, ?)";

		preStmt = cnn.prepareStatement(strSQL);

		preStmt.setString(1, isdn);
		preStmt.setInt(2, q.getId());
		preStmt.setInt(3, 1); // ANSWER_ORDER
		preStmt.setString(4, Enums.AnsStatus.WAITING);// ANSWER_STATUS
		preStmt.setInt(5, sessionId); // SESSION_ID
		preStmt.setString(6, q.getAnswer()); // EXPECTED_ANS
		preStmt.setInt(7, q.getPoints()); // EXPECTED_ANS
		preStmt.executeUpdate();
		preStmt.close();
	}
	
	
	
	private int adminSendQuestionToAllCustomers() {
		// 1. get list of sub from the beginning date until yesterday
		// 2. abort all session
		// 3. Close all question
		// 4. get question base on isdn
		// 5. send question
		int re = 0;
		ArrayList<String> isdns = getListofIsdn();
		for (String isdn : isdns) {
			int ansIn7day = getTotalOfAnsInPast(isdn, 7);
			if (ansIn7day > 0) {// trong vong 7 ngay phai co 1 giao dich
				if (sendQuestion(isdn)) {
					re++;
				}
			}
		}
		return 0;
	}

	private int getTotalOfAnsInPast(String isdn, int days) {
		DBPoolX smsPool = null;
		Connection cnn = null;
		int re = 0;
		try {
			smsPool = DBPoolX.getInstance(DBPoolXName.SERVICE_DEFAULT);
			cnn = smsPool.getConnection();
			PreparedStatement preStmt = cnn.prepareStatement(
					"select count(*) from MIL_ANSWER where ANSWER_ENDTIME >= DATE_SUB(current_timestamp, INTERVAL 7 DAY) and ISDN = ?");
			preStmt.setString(1, isdn);
			ResultSet rs = preStmt.executeQuery();
			if (rs.next()) {
				re = rs.getInt(1);
			}
			cnn.close();
		} catch (Exception e) {
				logger.error("error when getListofIsdn", e);
				return 0;
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(cnn, null);
			}
		}
		return re;
	}

	private ArrayList<String> getListofIsdn() {
		DBPoolX smsPool = null;
		Connection cnn = null;
		ArrayList<String> re = new ArrayList<String>();
		try {
			smsPool = DBPoolX.getInstance(DBPoolXName.SERVICE_DEFAULT);
			cnn = smsPool.getConnection();
			PreparedStatement preStmt = cnn.prepareStatement(
					"select distinct(s.isdn) from MIL_SESSION s " + 
					"where s.isdn not in (select c.isdn from MIL_CANCEL c) and s.last_send_time < DATE_SUB(current_timestamp, INTERVAL 12 HOUR) " +
					"and s.isdn not in (select s2.isdn from MIL_SESSION s2 where s2.last_send_time >= DATE_SUB(current_timestamp, INTERVAL 12 HOUR))");
			ResultSet rs = preStmt.executeQuery();
			if (rs.next()) {
				re.add(rs.getString(1));
			}
			cnn.close();
		} catch (Exception e) {
				logger.error("error when getListofIsdn", e);
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(cnn, null);
			}
		}
		return re;
	}

	private boolean sendQuestion(String isdn) {
		DBPoolX smsPool = null;
		Connection cnn = null;
		try {
			smsPool = DBPoolX.getInstance(DBPoolXName.SERVICE_DEFAULT);
			cnn = smsPool.getConnection();
			abortAnySession(isdn, cnn, true);
			updateAnswer(isdn, Enums.AnsStatus.ABORTED_SYS, cnn);
			
			String lang = getLastCorrectSessionLanguage(isdn);
			Question q = getRandomQuestion(isdn, cnn, lang);
			int sessionId = addNewSession(isdn, q.getId(), lang, cnn);
			addNewAns(isdn, q, sessionId, cnn);
			// TODO not good here
			if(Enums.Language.KHMER.equalsIgnoreCase(lang)) {
				SmsUtil.sendSms(isdn, "" + q.getPoints() + "P:" + q.getQuestion_KH(), SERVICE_NO, "Metfone");
			} else {
				SmsUtil.sendSms(isdn, "" + q.getPoints() + "P:" + q.getQuestion(), SERVICE_NO, "Metfone");
			}
			
			cnn.commit();
			cnn.close();
		} catch (Exception e) {
			logger.error("error when getListofIsdn", e);
			return false;
		} finally {
			if (smsPool != null) {
				smsPool.releaseConnection(cnn, null);
			}
		}
		return true;		
	}

	public static void main(String[] args) {
		SMSRequest re = new SMSRequest();
		re.setInfo("2");
		re.setCommandCode("2");
		re.setServiceNumber(SERVICE_NO);
		re.setSenderNumber("855979066460");

		new MillionareUnlimitProcessor().processRequest(re);

		int ah = 0;
		ah = 1;
		System.out.println(ah);
	}
}
